package db;
import java.sql.*;
import java.util.*;

import beans.*;

public class mzDAO extends ConnBean{
	private Connection connection=null;
	public List<mzBean> getAllRecords(){
		ResultSet rs=null;
		Statement stmt=null;
		ArrayList <mzBean> list=new ArrayList<mzBean>();
		try {	
			connection = getConnection();
			if(connection==null)
				System.out.println("error");
			String sql="select * from YY_mz";
			stmt =connection.createStatement();
			rs=stmt.executeQuery(sql);
			while(rs.next()){
				mzBean mz=new mzBean();
				mz.setId(rs.getString(1));
				mz.setName(rs.getString(2));
				mz.setKsid(rs.getString(3));
				list.add(mz);
			}
			stmt.close();						
			connection.close();			
			
	}catch (SQLException e) {
		e.printStackTrace();
	} finally{
		try{
			if (connection!=null && (!connection.isClosed())){
				connection.close();
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
		return list;
	}
	
	public mzBean getRecord(String mzid){
		ResultSet rs=null;
		Statement stmt=null;
		mzBean mz=null;
		try {			
			connection = getConnection();
			if(connection==null)
				System.out.println("error");
			String sql="select * from YY_mz where id='"+mzid+"'";
			stmt =connection.createStatement();
			rs=stmt.executeQuery(sql);
			if(rs!=null && rs.next()){
				mz=new mzBean();
				mz.setId(rs.getString(1));
				mz.setName(rs.getString(2));
				mz.setKsid(rs.getString(3));
			}
			else
			{	
				System.out.println("null");
				mz=null;
			}
			stmt.close();					
			connection.close();	
		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
		try{
			if (connection!=null && (!connection.isClosed())){
				connection.close();
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
		return mz;
  }	
	
	public boolean insertRecord(mzBean record)
	{
		PreparedStatement pstmt=null;
		String sql="insert into YY_mz values(?,?,?)";
		if(record==null) 
			return false;
		boolean result=false;
		int number=0;
		try{
			connection=getConnection();
			pstmt=connection.prepareStatement(sql);
			pstmt.setString(1, record.getId());
			pstmt.setString(2, record.getName());
			pstmt.setString(3, record.getKsid());
			number=pstmt.executeUpdate();
			pstmt.close();						
			connection.close();	
		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if (connection!=null && (!connection.isClosed())){
					connection.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(number>0)
			result=true;
		return result;
	}

	public boolean deleteRecord(mzBean record)
	{
		PreparedStatement pstmt=null;
		String sql="delete from YY_mz where id=?";
		
		if(record==null) 
			return false;
		int result=0;
		try{
			connection=getConnection();
			pstmt=connection.prepareStatement(sql);
			pstmt.setString(1, record.getId());
			result=pstmt.executeUpdate();
			System.out.println(record.getId());
			pstmt.close();						
			connection.close();	
		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if (connection!=null && (!connection.isClosed())){
					connection.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(result>0)
			return true;
		else
			return false;
	}
	
	public boolean updateRecord(mzBean beforeRecord,mzBean afterRecord)
	{
		if(beforeRecord==null || afterRecord==null) 
			return false;
		PreparedStatement pstmt=null;
		String sql="update YY_mz set id=?,name=?,ksid=?"+"where id="+beforeRecord.getId();
		int result=0;
		try{
			connection=getConnection();
			pstmt=connection.prepareStatement(sql);
			pstmt.setString(1, afterRecord.getId());
			pstmt.setString(2, afterRecord.getName());
			pstmt.setString(3, afterRecord.getKsid());
			result=pstmt.executeUpdate();
			pstmt.close();					
			connection.close();	
		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if (connection!=null && (!connection.isClosed())){
					connection.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(result>0)
			return true;
		else
			return false;
	}

	public List<mzBean> getmzs(String id) {
		ResultSet rs=null;
		Statement stmt=null;
		ArrayList <mzBean> list=new ArrayList<mzBean>();
		try {	
			connection = getConnection();
			if(connection==null)
				System.out.println("error");
			String sql="select * from YY_mz where ksid='"+id+"'";
			stmt =connection.createStatement();
			rs=stmt.executeQuery(sql);
			while(rs.next()){
				mzBean mz=new mzBean();
				mz.setId(rs.getString(1));
				mz.setName(rs.getString(2));
				mz.setKsid(rs.getString(3));
				list.add(mz);
			}
			stmt.close();						
			connection.close();			
			
	}catch (SQLException e) {
		e.printStackTrace();
	} finally{
		try{
			if (connection!=null && (!connection.isClosed())){
				connection.close();
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
		return list;
	}
	
}
